package com.itheima.wisdomnext.mapper;

import com.itheima.wisdomnext.entity.Post;
import com.itheima.wisdomnext.entity.PostTopic;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface PostMapper {

    // 插入新帖子，并回填生成的 id
    @Insert("INSERT INTO posts (userid, title, content, video_url, category_id, topic_id, views, likes, created_at) " +
            "VALUES (#{userid}, #{title}, #{content}, #{videoUrl}, #{categoryId}, #{topicId}, #{views}, #{likes}, NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")  // 回填 id
    void insertPost(Post post);

    // 根据用户 ID 获取用户的所有帖子
    @Select("SELECT * FROM posts WHERE userid = #{id}  AND banned != 1")
    List<Post> getPostById(int id);

    // 根据用户 ID 获取用户的所有帖子
    @Select("SELECT * FROM posts WHERE id = #{id}  AND banned != 1")
    Post getPostBypId(int pid);

    // 获取所有有效的帖子
    @Select("SELECT * FROM posts WHERE banned != 1 And approved != 0")
    List<Post> getAllPosts();

    // 根据分类 ID 获取所有帖子
    @Select("SELECT * FROM posts WHERE category_id = #{categoryId}  AND banned != 1")
    List<Post> findByCategoryId(@Param("categoryId") int categoryId);

    // 更新帖子的审核状态为通过
    @Update("UPDATE posts p SET p.approved = 1 WHERE p.id = #{id}")
    void updatePostStatusToApproved(int id);

    // 增加分类中的帖子数量
    @Update("UPDATE categories c SET c.post_count = c.post_count + 1 WHERE c.id = #{categoryId}")
    int incrementPostCountInCategory(int categoryId);

    // 获取某帖子的所有图片
    @Select("SELECT image_url FROM post_images WHERE post_id = #{postId}")
    List<String> getImagesByPostId(@Param("postId") int postId);

    // 根据多个用户 ID 获取对应用户的帖子
    @Select("<script>" +
            "SELECT * FROM posts WHERE userid IN " +
            "<foreach item='id' collection='list' open='(' separator=',' close=')'>#{id}</foreach> " +
            "AND banned != 1 AND approved != 0" +
            "</script>")
    List<Post> getPostsByUserIds(@Param("list") List<Integer> userIds);

    // 检查某个用户是否对某个帖子进行了点赞
    @Select("SELECT COUNT(*) FROM likes WHERE userid = #{userId} AND target_id = #{postId} AND target_type = 'POST'")
    int countLikesByUserAndPost(@Param("userId") int userId, @Param("postId") int postId);

    // 更新帖子中关联的问答帖子 ID
    @Update("UPDATE posts SET question_post_id = #{questionPostId} WHERE id = #{postId}")
    void updateQuestionPostId(@Param("postId") Integer postId, @Param("questionPostId") Integer questionPostId);

    // 根据话题 ID 获取所有帖子
    @Select("SELECT * FROM posts WHERE topic_id = #{topicId}")
    List<Post> getPostsByTopicId(@Param("topicId") Integer topicId);

    // 根据话题 ID 更新所有帖子的标题
    @Update("UPDATE posts SET title = #{title} WHERE topic_id = #{topicId}")
    int updatePostTitleByTopicId(@Param("topicId") Integer topicId, @Param("title") String title);

    // 查询符合条件的话题和帖子的联合数据（包括点赞数），并将 NULL 替换为 0
    @Select("SELECT " +
            "    NULL AS post_id, " +
            "    t.id AS topic_id, " +
            "    t.name AS name, " +
            "    COUNT(p.id) AS post_count, " +
            "    'topic' AS type, " +
            "    MAX(p.created_at) AS sort_time, " +
            "    SUM(p.likes) AS likes " +
            "FROM topics t " +
            "JOIN posts p ON t.id = p.topic_id " +
            "WHERE p.approved = 1 " +
            "GROUP BY t.id " +
            "UNION ALL " +
            "SELECT " +
            "    id AS post_id, " +
            "    NULL AS topic_id, " +
            "    title AS name, " +
            "    likes AS post_count, " +
            "    'post' AS type, " +
            "    created_at AS sort_time, " +
            "    likes AS likes " +
            "FROM posts " +
            "WHERE topic_id IS NULL AND approved = 1 " +
            "ORDER BY sort_time DESC " +
            "LIMIT 15")
    List<PostTopic> getRecentPostsOrTopics();

    @Select("SELECT * FROM posts WHERE id = #{id}")
    Post findById(@Param("id") Long id);

    @Update("UPDATE posts SET visibility = #{visibility} WHERE id = #{id}")
    int updateVisibility(@Param("id") Long id, @Param("visibility") String visibility);

    /**
     * 获取用户点赞的所有帖子
     *
     * @param userId 用户 ID
     * @return 用户点赞的帖子列表
     */
    @Select("SELECT p.* FROM posts p JOIN likes l ON p.id = l.target_id WHERE l.userid = #{userId} AND l.target_type = 'POST'")
    List<Post> getLikedPostsByUser(@Param("userId") int userId);
}
